<?php

namespace App\Model;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class CarBrand extends Model
{

    /**
     * 关联到模型的数据表
     *
     * @var string
     */
    protected $table = 'car_brand';

    public static function getModelsByBrandId( $brand_id ){

        $sql = "select m.id as model_id, m.name as model_name, f.name as factory_name, b.name as brand_name
                from car_model m 
                left join car_factory f on m.factory_id=f.id 
                left join car_brand b on b.id=f.brand_id 
                where b.id = ? ";
        $data = DB::select($sql,
            [ $brand_id ]);
        return $data;
    }

    public static function brand( $id ){

        $sql = "select * from car_brand where id=?";
        $data = DB::select( $sql, [ $id ] );
        return !empty( $data ) ? $data[0] : '';

    }

    public static function brands( $data ){

        $where = " where 1=1 ";
        if( !empty($data['name']) ){
            $where .= " and name like " . "'%".$data['name'] ."%'";
        }
        if( !empty($data['letter_pre']) ){
            $where .= " and letter_pre like " . "'%".$data['letter_pre'] ."%'";
        }

        $sql = "select * from car_brand" . $where;
        $data = DB::select( $sql );
        return $data;

    }

    public static function search( $data ){

        $where = [];

        if( !empty( $data['name'] ) ){
            $where[] =[ 'name', 'like', '%' . $data['name'] .'%'];
        }
        $list = CarBrand::where( $where )
//            ->leftJoin('car_brand','car_brand.id', '=', 'car_factory.brand_id')
//            ->forPage($page, $number)
            ->select(
                'letter_pre'
                , 'id'
                , 'name'
            )
            ->get();

        return $list;
    }

    public static function brand_edit( $id, $data){

        $now = Date("Y-m-d H:i:s");
        $sql = "update car_brand set 
                `name`=?, 
                updated_at='$now',
                letter_pre=? where id='".$id."'";
        $data = DB::update( $sql,[ $data['name'], $data['letter_pre'] ] );
        return $data;

    }

    public static function searchModelObjects( $search ){
        $sql = 'select * from car_model_object mb left join car_model m on mb.model_id=m.id where ';
        $where = '1=1 ';
        if( !empty( $search['year'] ) ){
            $where .= 'and year like %' . $search['year'] . '';
        }
        if( !empty( $search['engine_displacement'] ) ){

            $where .= 'and engine_displacement like %' . $search['engine_displacement'] . '';
        }
        $sql .= $where;

        $data = DB::select( $sql );
        return $data;
    }

    public static function modelObjects( $model_id ){
        $sql = 'select mo2.id, mo2.year, mo2.engine_displacement, mo2.label, mo2.mi_key,mo2.gearbox_lv,mo2.gearbox_type
                from car_model_object2 mo2 
                where mo2.model_id=? ';
        $data = DB::select( $sql,[ $model_id ] );
        return $data;
    }

    public static function modelObjectInfo( $mi_key ){

        $sql = 'select 
                        moi.oil,moi.oil_regional,moi.oil_change,moi.oil_img_url,
                        mo.gearbox_lv, mo.gearbox_type, mo.engine_displacement, mo.year,mo.label,
                        m.name as model_name, f.name as factory_name 
                        from car_model_object_info moi
                left join car_model_object2 mo on moi.mi_key=mo.mi_key
                left join car_model m on mo.model_id=m.id
                left join car_factory f on m.factory_id=f.id
                where moi.mi_key=?';
        $data = DB::select( $sql, [$mi_key] );
        $data = !empty( $data ) ? $data[0] : '';
        return $data;
    }

}